package web.dao;

import web.util.StringOperateUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by Administrator on 2016/7/8.
 */

@Repository
public class ToolDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    @Qualifier("jdbcTemplateYF")
    private JdbcTemplate jdbcTemplateYF;

    /**
     * 查询功能缓存
     *
     * @param key
     * @param proviceId
     * @param type
     * @return
     */
    public Map<String, Object> findIdJson(String key, int proviceId, int type) {
        String sql = "select id_json,id from cache_tool where `key`=? and type=?";
        if (type == 2) {
            sql += " and province_id=" + proviceId;
        }
        final Map<String, Object> map = new HashMap<String, Object>();
        jdbcTemplate.query(sql, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                map.put("id", rs.getInt("id"));
                map.put("id_json", rs.getString("id_json"));
            }
        }, key, type);
        return map;
    }

    /**
     * 添加缓存数据
     */
    public void insertToolCache(String json, String key, int province_id, int type) {
        String sql = "insert into cache_tool (`key`,`type`,province_id,id_json) values(?,?,?,?)";
        jdbcTemplate.update(sql, key, type, province_id, json);
    }



    /**
     * 查询success_news_num 中与subscriber相关的供应商
     *
     * @param subscriber
     * @param num        表名后缀
     * @return
     */
    public List<Map<String, Object>> findWinBidCompanyBySubscriber(String subscriber, final int num) {
        String sql = "select id,win_bid_company,subscriber from success_news_" + num + " where " +
                "match(subscriber_fc) against (? in boolean mode) and win_bid_company<>'' and status=0 limit 200";
        final List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        jdbcTemplateYF.query(sql, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("winBidCompany", rs.getString("win_bid_company"));
                map.put("subscriber", rs.getString("subscriber"));
                map.put("id", rs.getInt("id"));
                map.put("num", num);
                list.add(map);
            }
        }, subscriber);
        return list;
    }

    /**
     * 查询产品相关的供应商与招标单位
     *
     * @param keyWord
     * @param num
     * @param province
     * @return
     */
    public List<Map<String, Object>> findWinBidCompanyByKeyWord(String keyWord, final int num, int province) {
        String sql = "select id,win_bid_company from success_news_" + num + " where " +
                "match(content_fc) against(? in boolean mode) and win_bid_company <> '' and status=0 limit 200";
        if (province != 0) {
            sql += " and province_id=" + province;
        }
        final List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        jdbcTemplateYF.query(sql, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("id", rs.getInt("id"));
                map.put("winBidCompany", rs.getString("win_bid_company").trim());
                map.put("num", num);
                list.add(map);
            }
        }, keyWord);
        return list;
    }


    /**
     * 通过id查询success_news_num 中一条数据
     *
     * @param id
     * @param num
     * @return
     */
    public Map<String, Object> findSuccessfulNews(int id, String num) {
        String sql = "select id,title_key,win_bid_figure,subscriber,win_bid_company,title,url,successful_time" +
                ",province_id,successful_tender_news_id,city_id,FROM_UNIXTIME(successful_time,'%Y-%m-%d') as stime from " +
                " success_news_" + num + " where id=?";
        final Map<String, Object> map = new HashMap<String, Object>();
        jdbcTemplateYF.query(sql, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                map.put("id",rs.getInt("id")+"");
                map.put("successful_tender_news_id",rs.getInt("successful_tender_news_id")+"");
                map.put("title_key",rs.getString("title_key"));
                map.put("title", rs.getString("title"));
                map.put("url", rs.getString("url"));
                map.put("successful_time", rs.getInt("successful_time")+"");
                if (rs.getInt("successful_time") != 0) {
                    map.put("stime", rs.getString("stime"));
                } else {
                    map.put("stime", "");
                }
                map.put("winBidCompany", rs.getString("win_bid_company"));
                map.put("subscriber", rs.getString("subscriber"));
                map.put("win_bid_figure", rs.getString("win_bid_figure"));
                map.put("provinceId",rs.getString("province_id"));
                map.put("cityId",rs.getString("city_id"));

            }
        }, id);
        return map;
    }

    /**
     * 通过使用单位查询出该单位所有的联系人与联系电话
     * @param subscriber
     * @param provinceId
     * @return
     */
    public List<Map<String,Object>> findAllSubscriberContactBySubscriber(final String subscriber, String provinceId){
        String sql="select district_news_id,subscriber_contact,subscriber_phone,title_key,subscriber from tender_news_info_"+provinceId+
                " where match(subscriber_fc) against('"+ StringOperateUtil.returnKey(subscriber)+"' in boolean mode)" +
                " and status=0";
        final List<Map<String,Object>> list=new ArrayList<Map<String, Object>>();
        jdbcTemplate.query(sql, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                Map<String,Object>map=new HashMap<String, Object>();
                map.put("subscriber_contact",rs.getString("subscriber_contact"));
                map.put("subscriber_phone",rs.getString("subscriber_phone"));
                map.put("title_key",rs.getString("title_key"));
                map.put("district_news_id",rs.getString("district_news_id"));
                map.put("id",rs.getString("district_news_id").replaceAll("\\s+",""));
                if(rs.getString("subscriber").equals(subscriber)
                        && (!rs.getString("subscriber_contact").equals("")||!rs.getString("subscriber_phone").equals("")))
                    list.add(map);
            }
        });
        return list;
    }

    /**
     * 查询招标单位
     * @param districtNewsId
     * @param provinceId
     * @return
     */
    public Map<String,Object> findSubscriberById(String districtNewsId,String provinceId){
        String sql="select subscriber,title_key from tender_news_info_"+provinceId+" where district_news_id='"+districtNewsId+"'";
        final Map<String,Object> map=new HashMap<String, Object>();
        jdbcTemplate.query(sql, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                map.put("subscriber",rs.getString("subscriber"));
                map.put("title_key",rs.getString("title_key"));
            }
        });
        return map;
    }

}
